/* $RCSFile: ContactsDB.cs $
 * Apache license v2 2011 by Christian Surieux
 * $Source: ?/AddonNice/PageModules/Contacts/ContactsDB.cs $
 * $log$
 * Revision 19 2010/08/09 09:43:46  christian.surieux
 *   Added Log to cs sources files
 * Revision 6 2010/08/06 19:56:24  christian.surieux
 *   Added Log to All cs files
 */

using System;
using System.Diagnostics;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

using AddonNice;
using AddonNice.Configuration;
using AddonNice.Diagnostics;

namespace AddonNice.Settings.PageModules.Contacts
{

	/// <summary>
	/// Class that encapsulates all data logic necessary to add/query/delete
	/// contacts within the Portal database.
	/// </summary>
    public class ContactsDB : DBDataStore
    {
		/// <summary>
		/// The GetContacts method returns a DataSet containing all of the
		/// contacts for a specific portal module from the contacts
		/// database.
		///
		/// NOTE: A DataSet is returned from this method to allow this method to support
		/// both desktop and mobile Web UI.
		/// </summary>
		public DataSet GetContacts(int moduleID) 
        {
            SqlDataAdapter myAdapt			    =	new SqlDataAdapter("dbo.adn_GetContacts", myConnection);
            myAdapt.SelectCommand.CommandType   =	CommandType.StoredProcedure;

            AddModuleIDParameters(myAdapt.SelectCommand,moduleID);

			DataSet myDataSet					=	new DataSet();
			try
			{
				myAdapt.Fill(myDataSet);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ContactsDB GetContacts ex : {0}",exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
			// CS 12/04
			return CheckDataSetForDates(myDataSet);
		}
        
        public DataTable GetContactsGlobalized(int moduleID)
        {
            SqlDataAdapter myAdapt              =   new SqlDataAdapter("dbo.adn_GetContactsGlobalized", myConnection);
            myAdapt.SelectCommand.CommandType   =   CommandType.StoredProcedure;

			AddGlobalizeParameters(myAdapt.SelectCommand);
            AddModuleIDParameters(myAdapt.SelectCommand,moduleID);

            DataTable myDataTb = new DataTable();
            try
            {
                myAdapt.Fill(myDataTb);
            }
            catch (Exception exp)
            {
                if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ContactsDB GetContactsGlobalized moduleID: {0}, ex : {1}",moduleID, exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
            // CS 12/04
            return CheckDataTableForDates(myDataTb);
        }

        /// <summary>
		/// The GetSingleContact method returns a SqlDataReader containing details
		/// about a specific contact from the Contacts database table.
		/// </summary>
        public SqlDataReader GetSingleContact(int itemID) 
        {
            SqlCommand myCommand				=	new SqlCommand("dbo.adn_GetSingleContact", myConnection);
            myCommand.CommandType				=	CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);
            
			SqlDataReader result				=	null;
			try
			{
				myConnection.Open();
				result							= myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ContactsDB GetSingleContact itemID : {0}, ex : {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
			return result;
        }
        
        public SqlDataReader GetSingleContactGlobalized(int itemID) 
        {
            SqlCommand myCommand				=	new SqlCommand("dbo.adn_GetSingleContactGlobalized", myConnection);
            myCommand.CommandType				=	CommandType.StoredProcedure;

			AddGlobalizeParameters(myCommand);
            
            AddItemIDParameters(myCommand,itemID);
            
            SqlDataReader result				=	null;
			try
			{
				myConnection.Open();
				result							= myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ContactsDB GetSingleContactGlobalized itemID : {0}, ex : {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
			return result;
        }

        /// <summary>
		/// The DeleteContact method deletes the specified contact from
		/// the Contacts database table.
		/// </summary>
        public void DeleteContact(int itemID) 
        {
            SqlCommand myCommand				=	new SqlCommand("dbo.adn_DeleteContact", myConnection);
            myCommand.CommandType				=	CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ContactsDB DeleteContact itemID:{0} ex : {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

 		/// <summary>
		/// The DeleteContactGlobalized method deletes the specified contact from
		/// the Contacts database table.
		/// </summary>
        public void DeleteContactGlobalized(int itemID) 
        {
            SqlCommand myCommand				=	new SqlCommand("dbo.adn_DeleteContactGlobalized", myConnection);

            myCommand.CommandType				=	CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ContactsDB DeleteContactGlobalized itemID:{0} ex : {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        /// <summary>
		/// The AddContact method adds a new contact to the Contacts
		/// database table, and returns the ItemID value as a result.
		/// </summary>
		public int AddContact(int moduleID, int itemID, string userName, string name, string role, string email, string contact1, string contact2, string Fax, string Address) 
		{
			if (userName.Length < 1) 
			{
				userName					    =	StStr.unknown;
			}

			SqlCommand myCommand			    =	new SqlCommand("dbo.adn_AddContact", myConnection);
			myCommand.CommandType			    =	CommandType.StoredProcedure;

            SqlParameter parameterItemID		=   AddItemIDParameters(myCommand,itemID);
            parameterItemID.Direction           =   ParameterDirection.InputOutput;
            
            AddModuleIDParameters(myCommand,moduleID);

			SqlParameter parameterUserName	    =	new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
			parameterUserName.Value			    =	userName;
			myCommand.Parameters.Add(parameterUserName);

			SqlParameter parameterName		    =	new SqlParameter("@Name", SqlDbType.NVarChar, 100);
			parameterName.Value				    =	name;
			myCommand.Parameters.Add(parameterName);

			SqlParameter parameterRole		    =	new SqlParameter("@Role", SqlDbType.NVarChar, 100);
			parameterRole.Value				    =	role;
			myCommand.Parameters.Add(parameterRole);

			SqlParameter parameterEmail		    =	new SqlParameter("@Email", SqlDbType.NVarChar, 100);
			parameterEmail.Value			    =	email;
			myCommand.Parameters.Add(parameterEmail);

			SqlParameter parameterContact1	    =	new SqlParameter("@Contact1", SqlDbType.NVarChar, 100);
			parameterContact1.Value			    =	contact1;
			myCommand.Parameters.Add(parameterContact1);

			SqlParameter parameterContact2	    =	new SqlParameter("@Contact2", SqlDbType.NVarChar, 100);
			parameterContact2.Value			    =	contact2;
			myCommand.Parameters.Add(parameterContact2);

			SqlParameter parameterFax		    =	new SqlParameter("@Fax", SqlDbType.NVarChar, 100);
			parameterFax.Value				    =	Fax;
			myCommand.Parameters.Add(parameterFax);

			SqlParameter parameterAddress	    =	new SqlParameter("@Address", SqlDbType.NVarChar, 100);
			parameterAddress.Value			    =	Address;
			myCommand.Parameters.Add(parameterAddress);
			int ret							    =	-1;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				ret							    =	(int)parameterItemID.Value;
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ContactsDB DeleteArticle  itemID:{0} ex : {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}

            return ret;
        }

		/// <summary>
		/// The UpdateContact method updates the specified contact within
		/// the Contacts database table.
		/// </summary>
		public void UpdateContact(int moduleID, int itemID, string userName, string name, string role, string email, string contact1, string contact2, string Fax, string Address) 
		{

			if (userName.Length < 1) 
			{
				userName					=	StStr.unknown;
			}

			SqlCommand myCommand			=	new SqlCommand("dbo.adn_UpdateContact", myConnection);
			myCommand.CommandType			=	CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

			SqlParameter parameterUserName	=	new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
			parameterUserName.Value			=	userName;
			myCommand.Parameters.Add(parameterUserName);

			SqlParameter parameterName		=	new SqlParameter("@Name", SqlDbType.NVarChar, 100);
			parameterName.Value				=	name;
			myCommand.Parameters.Add(parameterName);

			SqlParameter parameterRole		=	new SqlParameter("@Role", SqlDbType.NVarChar, 100);
			parameterRole.Value				=	role;
			myCommand.Parameters.Add(parameterRole);

			SqlParameter parameterEmail		=	new SqlParameter("@Email", SqlDbType.NVarChar, 100);
			parameterEmail.Value			=	email;
			myCommand.Parameters.Add(parameterEmail);

			SqlParameter parameterContact1	=	new SqlParameter("@Contact1", SqlDbType.NVarChar, 100);
			parameterContact1.Value			=	contact1;
			myCommand.Parameters.Add(parameterContact1);

			SqlParameter parameterContact2	=	new SqlParameter("@Contact2", SqlDbType.NVarChar, 100);
			parameterContact2.Value			=	contact2;
			myCommand.Parameters.Add(parameterContact2);

			SqlParameter parameterFax		=	new SqlParameter("@Fax", SqlDbType.NVarChar, 100);
			parameterFax.Value				=	Fax;
			myCommand.Parameters.Add(parameterFax);

			SqlParameter parameterAddress	=	new SqlParameter("@Address", SqlDbType.NVarChar, 100);
			parameterAddress.Value			=	Address;
			myCommand.Parameters.Add(parameterAddress);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ContactsDB UpdateContact itemID:{0} ex : {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        public void UpdateContactGlobalized(int moduleID, int itemID, string userName, string name, string role, string email, string contact1, string contact2, String Fax, String Address) 
        {

            if (userName.Length < 1)
            {
                userName                        =   StStr.unknown;
            }

            SqlCommand myCommand                =   new SqlCommand("dbo.adn_UpdateContactGlobalized", myConnection);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            AddModuleIDParameters(myCommand,moduleID);

            AddGlobalizeParameters(myCommand);

            AddItemIDParameters(myCommand,itemID);

            SqlParameter parameterUserName      =   new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
            parameterUserName.Value             =   userName;
            myCommand.Parameters.Add(parameterUserName);

            SqlParameter parameterName          =   new SqlParameter("@Name", SqlDbType.NVarChar, 100);
            parameterName.Value                 =   name;
            myCommand.Parameters.Add(parameterName);

            SqlParameter parameterRole          =   new SqlParameter("@Role", SqlDbType.NVarChar, 100);
            parameterRole.Value                 =   role;
            myCommand.Parameters.Add(parameterRole);

            SqlParameter parameterEmail         =   new SqlParameter("@Email", SqlDbType.NVarChar, 100);
            parameterEmail.Value                =   email;
            myCommand.Parameters.Add(parameterEmail);

            SqlParameter parameterContact1      =   new SqlParameter("@Contact1", SqlDbType.NVarChar, 100);
            parameterContact1.Value             =   contact1;
            myCommand.Parameters.Add(parameterContact1);

            SqlParameter parameterContact2      =   new SqlParameter("@Contact2", SqlDbType.NVarChar, 100);
            parameterContact2.Value             =   contact2;
            myCommand.Parameters.Add(parameterContact2);

            SqlParameter parameterFax           =   new SqlParameter("@Fax", SqlDbType.NVarChar, 100);
            parameterFax.Value                  =   Fax;
            myCommand.Parameters.Add(parameterFax);

            SqlParameter parameterAddress       =   new SqlParameter("@Address", SqlDbType.NVarChar, 100);
            parameterAddress.Value              =   Address;
            myCommand.Parameters.Add(parameterAddress);

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ContactsDB UpdateContactGlobalized  moduleID: {0}, itemID: {1}, ex: {2}",moduleID, itemID, exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
        }
    }
}

